package com.cjn.dbtool.service;

import com.cjn.dbtool.entity.fieldMsg;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * AUTHOR chenjianan
 * TIME  2017-11-10 11:46
 * REMARK
 */

public class MysqlTool {

    Logger logger = LoggerFactory.getLogger(MysqlTool.class);

    public  void getMessageForDb(String url, String user, String pwd,String targetAddress){

        //生成样式文件夹
        makeCssFile(targetAddress);

        Connection conn = null;
        DatabaseMetaData metaData = null;
        Statement st = null;
        ResultSet rs = null;
        ResultSet crs = null;

        try {
            //加载驱动
            Class.forName("com.mysql.jdbc.Driver");
            //获得链接
            conn = DriverManager.getConnection(url, user, pwd);
            //准备查询组件
            st = conn.createStatement();
            //获取所有数据库名
            ResultSet dbNameRs = st.executeQuery("SELECT SCHEMA_NAME FROM SCHEMATA");
            //数据库 <---> 表名  map
            Map<String,Object> dataBase = new HashMap<String, Object>();
            //数据库名称 list
            List<String> dataBaseList = new ArrayList<String>();
            //遍历每个库
            while(dbNameRs.next()){
                //得到库名
                String dbName = dbNameRs.getString("SCHEMA_NAME");
                if("information_schema".equals(dbName) || "mysql".equals(dbName)){
                    logger.info("系统库，不打印");
                }else{
                    dataBaseList.add(dbName);
                    //获取到数据库元数据
                    metaData = conn.getMetaData();
                    // 表名 <--->字段 map
                    Map<String,Object> map = new HashMap<String, Object>();
                    // 表名 list
                    List<String> strs= new ArrayList<String>();
                    //获取到所有表名
                    rs = metaData.getTables(dbName, "%", "%", new String[] { "TABLE" });
                    //遍历表
                    while (rs.next()) {
                        //表名
                        String tablename = rs.getString("TABLE_NAME");
                        //表注释
                        String tableRemark = rs.getString("REMARKS");
                        String tableMsg = tablename+"***"+tableRemark;
                        strs.add(tableMsg);
                        //得到字段信息
                        ResultSet rs2 = conn.getMetaData().getColumns(dbName,"%",tablename.toUpperCase(), "%");
                        //字段list
                        List<fieldMsg> list = new ArrayList<fieldMsg>();
                        //遍历一张表的字段信息
                        while(rs2.next()){
                            fieldMsg en= new fieldMsg();
                            //字段名
                            en.setName(rs2.getString("COLUMN_NAME"));
                            //字段数据类型
                            en.setType(rs2.getString("TYPE_NAME"));
                            //字段注释
                            en.setRemark(rs2.getString("REMARKS"));

                            list.add(en);
                        }
                        map.put(tablename,list);
                    }
                    dataBase.put(dbName,strs);
                    //去生成该表对应的html
                    makeHtml(dbName,strs,map,targetAddress);
                    logger.info(dbName+"库的表对应的HTML生成完毕！");

                }

            }
            //生成目录的 HTML
            makeHtmlForIndex(dataBaseList,dataBase,targetAddress);

            logger.info("目录导航生成完毕");
        } catch (ClassNotFoundException e) {
            logger.debug("类找不到异常",e);
            e.printStackTrace();
        } catch (SQLException e) {
            logger.debug("SQL异常",e);
            e.printStackTrace();
        }finally {
            try {
                conn.close();
            } catch (SQLException e) {
                logger.debug("SQL连接关闭异常",e);
                e.printStackTrace();
            }
        }
    }

    public   void makeCssFile(String targetAddress) {
        InputStream fileinputstream = null;// 读取模板文件
        FileOutputStream fileoutputstream = null;

        try {
            //建立 输入流 导入模板文件
            fileinputstream= MysqlTool.class.getResourceAsStream("/template/bootstrap.min.css");

            //获取文件长度
            int lenght = fileinputstream.available();
            //建立字节数组
            byte bytes[] = new byte[lenght];
            //读入模板文件
            fileinputstream.read(bytes);
            //关闭输入流
            fileinputstream.close();
            logger.info("导入css模板文件完毕");

            String address =targetAddress+"/bootstrap/bootstrap.min.css";
            //生成文件
            File file = new File(address);
            File fileParent = file.getParentFile();
            //判断table目录是否存在
            if(!fileParent.exists()){
                fileParent.mkdirs();
            }
            file.createNewFile();
            // 建立文件输出流
            fileoutputstream = new FileOutputStream(file);

            //写入文件
            fileoutputstream.write(bytes);
            //关闭流
            fileoutputstream.close();
            logger.info("导出css模板文件完毕");

        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            if(fileinputstream != null){
                try {
                    fileinputstream.close();
                } catch (IOException e) {
                    logger.debug("输入流关闭异常",e);
                    e.printStackTrace();
                }
            }
            if (fileoutputstream != null){
                try {
                    fileoutputstream.close();
                } catch (IOException e) {
                    logger.debug("输出流关闭异常",e);
                    e.printStackTrace();
                }
            }
        }

    }

    public   void makeHtmlForIndex(List<String> dataBaseList, Map<String, Object> dataBase,String targetAddrress) {

        InputStream fileinputstream = null;// 读取模板文件
        FileOutputStream fileoutputstream = null;
        String templateContent="";

        try {
            //建立 输入流 导入模板文件
            fileinputstream= MysqlTool.class.getResourceAsStream("/template/index.html");
            //fileinputstream = new FileInputStream("D:\\E\\index.html");
            //获取文件长度
            int lenght = fileinputstream.available();
            //建立字节数组
            byte bytes[] = new byte[lenght];
            //读入模板文件
            fileinputstream.read(bytes);
            //关闭输入流
            fileinputstream.close();
            logger.info("导入目录导航模板文件完毕");
            //转为字符串
            templateContent = new String(bytes);

            //新的内容模块
            StringBuilder sb = new StringBuilder();
            sb.append("<h4>数据库表目录</h4>");
            sb.append("<ul>");
            for (String str:dataBaseList) {//库名
                List<String> tableName = (List<String>) dataBase.get(str);//表名
                sb.append("<li>");
                sb.append("<span onclick=\"f('" + str + "')\">"+str+"</span>");
                sb.append("<ul id='"+str+"' style='display: none;' >");
                for (String table: tableName) {
                    String name = table.substring(0,table.indexOf("*"));
                    sb.append("<li><a href='table/"+str+"_"+name+".html' target=\"myFrameName\">"+name+"</a></li>");
                }
                sb.append("</ul>");
                sb.append("</li>");
            }
            sb.append("</ul>");

            //将新的内容替换进字符串
            templateContent = templateContent.replaceAll("###title###", sb.toString());

            //创建输出流
            File file = new File(targetAddrress+"/dbIndex.html");
            // 建立文件输出流
            fileoutputstream = new FileOutputStream(file);
            //转换成字符数组
            byte tag_bytes[] = templateContent.getBytes();
            //写入文件
            fileoutputstream.write(tag_bytes);
            //关闭流
            fileoutputstream.close();

        } catch (FileNotFoundException e) {
            logger.debug("文件找不到",e);
            e.printStackTrace();
        } catch (IOException e) {
            logger.debug("IO异常",e);
            e.printStackTrace();
        }finally {
            if(fileinputstream != null){
                try {
                    fileinputstream.close();
                } catch (IOException e) {
                    logger.debug("输入流关闭异常",e);
                    e.printStackTrace();
                }
            }
            if (fileoutputstream != null){
                try {
                    fileoutputstream.close();
                } catch (IOException e) {
                    logger.debug("输出流关闭异常",e);
                    e.printStackTrace();
                }
            }
        }
    }

    public   void makeHtml(String dbName,List<String> list,Map<String,Object> map,String targetAddress){

        //开始写html
        for (String str: list) {
            OutputStream os = null;
            try {
                //表名
                String tableName = str.substring(0,str.indexOf("*"));
                //表注释
                String tableReamrk = str.substring(str.indexOf("*")+3);
                String address =targetAddress+"/table/"+dbName+"_"+tableName+".html";
                //生成文件
                File file = new File(address);
                File fileParent = file.getParentFile();
                //判断table目录是否存在
                if(!fileParent.exists()){
                    fileParent.mkdirs();
                }
                file.createNewFile();
                //转换字段信息
                List<fieldMsg> listFieldMsg = (List<fieldMsg>) map.get(tableName);
                //建立输出流
                os = new FileOutputStream(file);
                //拼接网页内容
                StringBuilder sb = new StringBuilder();
                sb.append("<html>");
                sb.append("<head>");
                sb.append("<title>"+tableName+"</title>");
                sb.append("<meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\" />");
                sb.append("<link href=\"../bootstrap/bootstrap.min.css\" rel=\"stylesheet\" type=\"text/css\"/>");

                sb.append("</head>");
                sb.append("<body>");
                sb.append("<div align=\"center\">");
                sb.append("<div><h3>表名:"+tableName+"</h3><p>表注释:"+tableReamrk+"+</p></div>");
                sb.append("<table class=\"table table-striped\">");
                sb.append("<tr><td>字段名</td><td>字段类型</td><td>字段注释</td>");
                sb.append("</tr>");
                //循环添加字段信息
                for (fieldMsg abc: listFieldMsg) {
                    sb.append("<tr>");
                    sb.append("<td>"+abc.getName()+"</td>");
                    sb.append("<td>"+abc.getType()+"</td>");
                    sb.append("<td>"+abc.getRemark()+"</td>");
                    sb.append("</tr>");
                }
                sb.append("</table>");
                sb.append("</div></body></html>");
                //写入文件
                os.write(sb.toString().getBytes());
                //关闭流
                os.close();
            } catch (FileNotFoundException e) {
                logger.debug("文件找不到",e);
                e.printStackTrace();
            } catch (IOException e) {
                logger.debug("IO异常",e);
                e.printStackTrace();
            }finally {
                if(os != null){
                    try {
                        os.close();
                    } catch (IOException e) {
                        logger.debug("输出流关闭异常！",e);
                        e.printStackTrace();
                    }
                }
            }
        }
    }

}
